library(RODBC)
#create a connection to databases
newDB = odbcDriverConnect('Driver={MySQL ODBC 5.1 Driver};SERVER=mapserve.hpc.jcu.edu.au;Uid=mapusr;Pwd=mapusr;')

MVD = odbcDriverConnect('Driver={Microsoft Access Driver (*.mdb)};Dbq=\\\\Zl5570-jeremy\\MVD_Database\\MVD.mdb;SystemDB=\\\\Zl5570-jeremy\\MVD_Database\\Security.mdw;Uid=ctbcc;Pwd=admin;')
MID = odbcDriverConnect('Driver={Microsoft Access Driver (*.mdb)};Dbq=\\\\Zl5570-jeremy\\MVD_Database\\MID.mdb;SystemDB=\\\\Zl5570-jeremy\\MVD_Database\\Security.mdw;Uid=ctbcc;Pwd=admin;')
MVDenviro = odbcDriverConnect('Driver={Microsoft Access Driver (*.mdb)};Dbq=\\\\Zl5570-jeremy\\MVD_Database\\MVD_environment.mdb;SystemDB=\\\\Zl5570-jeremy\\MVD_Database\\Security.mdw;Uid=ctbcc;Pwd=admin;')

#find tables in:
#newDB
tquery="select table_name as Name from INFORMATION_SCHEMA.Tables where TABLE_TYPE ='BASE TABLE'"
DBdata=sqlQuery(newDB,tquery)
colnames(DBdata)='tables'

#MVD
tquery="SELECT Name FROM MSysObjects
WHERE TYPE = 1
ORDER BY Name"

MVDdata=sqlQuery(MVD,tquery)
tables=c(1:119,132:203)
MVDdata=MVDdata[tables,]
MVDdata=as.data.frame(MVDdata)
colnames(MVDdata)='tables'

#MID
MIDdata=sqlQuery(MID,tquery)
MIDdata
tables=c(1:16,29:33)
MIDdata=MIDdata[tables,]
MIDdata=as.data.frame(MIDdata)
colnames(MIDdata)='tables'

#MVDenviro
MVDenviro.data=sqlQuery(MVDenviro,tquery)
MVDenviro.data
tables=c(1:29,42:57)
MVDenviro.data=MVDenviro.data[tables,]
MVDenviro.data=as.data.frame(MVDenviro.data)
colnames(MVDenviro.data)='tables'

#combine the data
MEDdata=(rbind(MVDdata,MIDdata,MVDenviro.data))
#convert table names to lowercase and sep by underscore
MEDdata$tables=gsub(' ','_',MEDdata$tables)
MEDdata$tables=tolower(MEDdata$tables)

compare=intersect(MEDdata$tables,DBdata$tables)
DBdata.missing=setdiff(MEDdata$tables,DBdata$tables)
MEDdata.missing=setdiff(DBdata$tables,MEDdata$tables)

wd='E:checking_MED/';setwd(wd)
library(SDMTools)
write.csv(compare, 'tables_shared.csv')
write.csv(DBdata.missing, 'tables_uniqueto_accessDB.csv')
write.csv(MEDdata.missing, 'tables_uniqueto_mysqlDB.csv')
write.csv(MEDdata, 'tables_accessDB.csv')
write.csv(DBdata, 'tables_mysqlDB.csv')

###################In tables that overlap, find values that overlap######################
library(SDMTools)
wd='E:checking_MED/test/';setwd(wd)
tables=read.csv('tables_shared.csv')

#find the dimensions of each table in the mysql DB that is shared with the access DB

DBdims=matrix(NA, nr=nrow(tables), nc=3)
DBdims=as.data.frame(DBdims)
colnames(DBdims)=c('tables', 'row', 'col')

jj=0
next.row=function(r){r=r+1;return(r)}

for(tbl in tables$x) { cat(tbl,'\n')

jj=next.row(jj)
if (tbl=='invertebrate_samples'|tbl=='append_logdata'|tbl=='append_readings'|tbl=='ibuttonmissions'|tbl=='ibuttonmissions2check'|tbl=='ibuttonmissions2check_backup'|tbl=='microhabitat_samples'|tbl=='microhabitat_subsamples'|tbl=='npp_dend'|tbl=='npp_dend_meas'|tbl=='npp_density'|tbl=='npp_leaf_litt'|tbl=='npp_leaf_litt_wght'|tbl=='rpt_thermo_import_data'|tbl=='site_vegetation_description'){DBdims[jj,1]=tbl;DBdims[jj,c(2,3)]='broken - date'
	} else {
tquery=paste("SELECT * FROM med.", tbl ,sep='')
DBdata=sqlQuery(newDB,tquery)
a=dim(DBdata)
DBdims[jj,1]=tbl
if(is.null(a[1])){DBdims[jj,2]=NA
	} else {DBdims[jj,2]=a[1]}
if(is.null(a[2])){DBdims[jj,3]=NA
	} else {DBdims[jj,3]=a[2]}
	}
}

#for those tables with datetime, apply a direct count query
missing=c('invertebrate_samples','append_logdata','append_readings','ibuttonmissions','ibuttonmissions2check','ibuttonmissions2check_backup','microhabitat_samples','microhabitat_subsamples','npp_dend','npp_dend_meas','npp_density','npp_leaf_litt','npp_leaf_litt_wght','rpt_thermo_import_data','site_vegetation_description')

for (tbl in missing) {

rquery=paste("SELECT COUNT(*) from med.", tbl,sep='')
rdata=sqlQuery(newDB,rquery)
rdata

cquery=paste("SELECT COUNT(*) 
			FROM INFORMATION_SCHEMA.COLUMNS 
			WHERE table_schema = 'med'   
			AND table_name ='",tbl,"'",sep='')
cdata=sqlQuery(newDB,cquery)
cdata

rowname=which(DBdims$table==tbl)

DBdims[rowname,2]=rdata
DBdims[rowname,3]=cdata
}
save(file='DBdims.rData')

#find the dims in the access DB, first by finding all the tables

tquery="SELECT Name FROM MSysObjects
WHERE TYPE = 1
ORDER BY Name"

MVDdata=sqlQuery(MVD,tquery)
tables=c(1:119,132:203)
MVDdata=MVDdata[tables,]
MVDdata=as.data.frame(MVDdata)
colnames(MVDdata)='tables'

#MID
MIDdata=sqlQuery(MID,tquery)
MIDdata
tables=c(1:16,29:33)
MIDdata=MIDdata[tables,]
MIDdata=as.data.frame(MIDdata)
colnames(MIDdata)='tables'

#MVDenviro
MVDenviro.data=sqlQuery(MVDenviro,tquery)
MVDenviro.data
tables=c(1:29,42:57)
MVDenviro.data=MVDenviro.data[tables,]
MVDenviro.data=as.data.frame(MVDenviro.data)
colnames(MVDenviro.data)='tables'

#combine the data
MEDdata=(rbind(MVDdata,MIDdata,MVDenviro.data))

MEDdata$tablenames=MEDdata$tables
MEDdata$tables=gsub(' ','_',MEDdata$tables)
MEDdata$tables=tolower(MEDdata$tables)
MEDdata$element=is.element(MEDdata$tables,DBdims$tables)
copy.MEDdata=MEDdata
copy.MEDdata=subset(copy.MEDdata, element=='TRUE',select=c(tables,tablenames))

MEDdims=matrix(NA, nr=nrow(copy.MEDdata), nc=3)
MEDdims=as.data.frame(MEDdims)
colnames(MEDdims)=c('tables', 'row', 'col')


jj=0
next.row=function(r){r=r+1;return(r)}

for(ii in 1:187) { cat(ii,'\n')

jj=next.row(jj)
if (ii==154){MEDdims[jj,1]='species';MEDdims[jj,2]='broken - unknown';MEDdims[jj,3]='broken - unknown'
	} else {
tblname=copy.MEDdata[ii,1]
tbl=copy.MEDdata[ii,2]

tquery=paste("SELECT * FROM ", tbl ,sep='')
tdata=sqlQuery(MVD,tquery)
a=dim(tdata)
MEDdims[jj,1]=as.character(tblname)
if(is.null(a[1])){MEDdims[jj,2]=NA
	} else {MEDdims[jj,2]=a[1]}
if(is.null(a[2])){MEDdims[jj,3]=NA
	} else {MEDdims[jj,3]=a[2]}
}
}

for(ii in 188:201) { cat(ii,'\n')
tblname=copy.MEDdata[ii,1]
tbl=copy.MEDdata[ii,2]
jj=next.row(jj)

tquery=paste("SELECT * FROM ", tbl ,sep='')
tdata=sqlQuery(MID,tquery)
a=dim(tdata)
MEDdims[jj,1]=as.character(tblname)
if(is.null(a[1])){MEDdims[jj,2]=NA
	} else {MEDdims[jj,2]=a[1]}
if(is.null(a[2])){MEDdims[jj,3]=NA
	} else {MEDdims[jj,3]=a[2]}

}

for(ii in 202:nrow(copy.MEDdata)) { cat(ii,'\n')
tblname=copy.MEDdata[ii,1]
tbl=copy.MEDdata[ii,2]
jj=next.row(jj)

tquery=paste("SELECT * FROM ", tbl ,sep='')
tdata=sqlQuery(MVDenviro,tquery)
a=dim(tdata)
MEDdims[jj,1]=as.character(tblname)
if(is.null(a[1])){MEDdims[jj,2]=NA
	} else {MEDdims[jj,2]=a[1]}
if(is.null(a[2])){MEDdims[jj,3]=NA
	} else {MEDdims[jj,3]=a[2]}

}
MEDdims=MEDdims[c(1:229,231:245),]
save(file='MEDdims.rData')

DBdims$row=as.numeric(DBdims$row)
DBdims$col=as.numeric(DBdims$col)
MEDdims$row=as.numeric(MEDdims$row)
MEDdims$col=as.numeric(MEDdims$col)


#find the tables within both DBs that share the same dims
compare=MEDdims[which(MEDdims$row==DBdims$row & MEDdims$col==DBdims$col),]

#find the tables that HAVE dims in each DB
MEDhasR=setdiff(MEDdims$row,DBdims$row)
DBhasR=setdiff(DBdims$row,MEDdims$row)
MEDhasC=setdiff(MEDdims$col,DBdims$col)
DBhasC=setdiff(DBdims$col,MEDdims$col)

sumMEDr=NULL
for (ii in MEDhasR) {
if (length(sumMEDr)==1){
	sumMEDr=MEDdims[which(MEDdims$row==ii),]
	} else {sumMEDr=rbind(sumMEDr,MEDdims[which(MEDdims$row==ii),])}
}
sumMEDc=NULL
for (ii in MEDhasC){
if (length(sumMEDc)==1){
	sumMEDc=MEDdims[which(MEDdims$col==ii),]
	} else {sumMEDc=rbind(sumMEDc,MEDdims[which(MEDdims$col==ii),])}
}
sumMED=merge(sumMEDr,sumMEDc,all=T)

sumDBr=NULL
for (ii in DBhasR) {
if (length(sumDBr)==1){
	sumDBr=DBdims[which(DBdims$row==ii),]
	} else {sumDBr=rbind(sumDBr,DBdims[which(DBdims$row==ii),])}
}
sumDBc=NULL
for (ii in DBhasC){
if (length(sumDBc)==1){
	sumDBc=DBdims[which(DBdims$col==ii),]
	} else {sumDBc=rbind(sumDBc,DBdims[which(DBdims$col==ii),])}
}
sumDB=merge(sumDBr,sumDBc,all=T)


write.csv(MEDdims,'dims_accessDB.csv')
write.csv(DBdims,'dims_mysqlDB.csv')
write.csv(compare,'dims_shared.csv')
write.csv(sumMED,'dims_uniqueto_accessDB.csv')
write.csv(sumDB,'dims_uniqueto_mysqlDB.csv')


###################In tables that share the same dims in each DB, compare values###########
library(SDMTools)
wd='E:checking_MED/';setwd(wd)
tables=read.csv('dims_shared.csv')

DBtables=tables$tables
MEDdata$element=is.element(MEDdata$tables,DBtables)
MEDtables.copy=subset(MEDdata, element=='TRUE',select=c(tablenames,tables))
MEDtables=MEDtables.copy$tablenames


compare.values=as.data.frame(DBtables); compare.values$tablenames=MEDtables;compare.values$TF=NA
jj=0
for (ii in 1:length(DBtables)){
jj=next.row(jj)
if (DBtables[ii]=='invertebrate_samples'|DBtables[ii]=='append_logdata'|DBtables[ii]=='append_readings'|DBtables[ii]=='ibuttonmissions'|DBtables[ii]=='microhabitat_samples'){compare.values[jj,]=DBtables[ii];compare.values[jj,3]='broken - date'
	} else {

DBquery=paste("SELECT * FROM med.",DBtables[ii],sep='')
DBdata=sqlQuery(newDB,DBquery)

MEDquery=paste("SELECT * FROM ",MEDtables[ii],sep='')
if (ii<100) {MEDdata=sqlQuery(MVD,MEDquery);colnames(MEDdata)=colnames(DBdata)}
if (ii>=100 & ii <110) {MEDdata=sqlQuery(MID,MEDquery);colnames(MEDdata)=colnames(DBdata)}
if (ii >=110) {MEDdata=sqlQuery(MVDenviro,MEDquery);colnames(MEDdata)=colnames(DBdata)}

if (isTRUE(all.equal(DBdata[,],MEDdata[,]))) {compare.values[jj,3]=T
	} else { compare.values[jj,3]=F }
}
}
write.csv(compare.values, 'value.comparison1.csv')

colnames(compare.values)=c('tables','tablenames','TF')
DBfalse=subset(compare.values, TF=='FALSE',select=c(tables))

MEDtables.copy$element=is.element(MEDtables.copy$tables,DBfalse[,1])
MEDfalse=subset(MEDtables.copy, element=='TRUE',select=c(tablenames))
DBfalse=DBfalse[,1]
MEDfalse=MEDfalse$tablenames

compare.false=as.data.frame(DBfalse); compare.false$TF=NA


jj=0

for (ii in 1:length(DBfalse)){
jj=next.row(jj)

DBquery=paste("SELECT * FROM med.",DBfalse[ii],sep='')
DBdata=sqlQuery(newDB,DBquery)

MEDquery=paste("SELECT * FROM ",MEDfalse[ii],sep='')
if (ii<24) {MEDdata=sqlQuery(MVD,MEDquery);colnames(MEDdata)=colnames(DBdata)}
if (ii>=24 & ii <31) {MEDdata=sqlQuery(MID,MEDquery);colnames(MEDdata)=colnames(DBdata)}
if (ii >=31) {MEDdata=sqlQuery(MVDenviro,MEDquery);colnames(MEDdata)=colnames(DBdata)}

DBdata=DBdata[order(DBdata[1]),];rownames(DBdata)=rownames(DBdata)
MEDdata=MEDdata[order(MEDdata[1]),];rownames(MEDdata)=rownames(DBdata)

for (tt in 1:ncol(DBdata)) {
	a=setdiff(DBdata[tt],MEDdata[tt])
	if (is.null(a[1,1])) {
	} else { compare.false[jj,2]=F;tt=ncol(DBdata) }
}
if (is.null(a[1,1]) ){compare.false[jj,2]=T}


}
write.csv(compare.false,'ordered_tables.csv')

DBunordered=subset(compare.false, TF=='FALSE',select=c(DBfalse))
MEDfalse=as.data.frame(MEDfalse);colnames(MEDfalse)='tablenames'; MEDfalse$tables=DBfalse
MEDfalse$element=is.element(MEDfalse$tables, DBunordered$DBfalse)
MEDunordered=subset(MEDfalse, element=='TRUE',select=c(tablenames))
DBunordered=DBunordered[,1]
MEDunordered=MEDunordered$tablenames


for (ii in 1:length(DBunordered)) {
DBquery=paste("SELECT * FROM med.",DBunordered[ii],sep='')
DBdata=sqlQuery(newDB,DBquery)

MEDquery=paste("SELECT * FROM ",MEDunordered[ii],sep='')
if (ii<4) {MEDdata=sqlQuery(MVD,MEDquery);colnames(MEDdata)=colnames(DBdata)}
if (ii ==4) {MEDdata=sqlQuery(MVDenviro,MEDquery);colnames(MEDdata)=colnames(DBdata)}

DBdata=DBdata[order(DBdata[1]),]
MEDdata=MEDdata[order(MEDdata[1]),]

tdata=DBdata==MEDdata
save(tdata,file=paste(DBunordered[ii],'.rData',sep=''))
pos= which(!tdata, arr.ind=TRUE)
pos=as.data.frame(pos,row.names=1:nrow(pos))
write.csv(pos, paste(DBunordered[ii],'.csv',sep=''))
pos$row=as.factor(pos$row);pos$col=as.factor(pos$col)
s=summary(pos,maxsum=nrow(pos))
save(s,file=paste('summary.',DBunordered[ii],'.rData',sep=''))

}




#################another trial
v=NULL
for (tt in 1:ncol(DBdata)) {
	a=setdiff(DBdata[tt],MEDdata[tt])
	if (length(v)==0){ 
	if (is.null(a[1,1])) { v=T
	} else { v=F }
	} else { if (is.null(a[1,1])) { v=c(v,T)
	} else { v=c(v,F) }}
}

a=setdiff(DBdata[tt],MEDdata[tt])
a
class(a[1,1])

a=setdiff(MEDdata[tt],DBdata[tt])
a
class(a[1,1])









